﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace Wild2.Models.User
{
    public class Subscription
    {
        public static void Subscribe(int kto, int kogo)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO Subscribes(Kto, Kogo) VALUES(@Kto, @Kogo)", conn);
                cmd.Parameters.Add("@KTO", SqlDbType.Int);
                cmd.Parameters["@KTO"].Value = kto;
                cmd.Parameters.Add("@KOGO", SqlDbType.Int);
                cmd.Parameters["@KOGO"].Value = kogo;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
        }

        public static void Unsubscribe(int kto, int kogo)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"DELETE FROM Subscribes WHERE(Kto = @KTO) AND (Kogo = @KOGO)", conn);
                cmd.Parameters.Add("@KTO", SqlDbType.Int);
                cmd.Parameters["@KTO"].Value = kto;
                cmd.Parameters.Add("@KOGO", SqlDbType.Int);
                cmd.Parameters["@KOGO"].Value = kogo;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
        }

        public static bool isSubscribed(int kto, int kogo)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT COUNT(*) as CNT FROm Subscribes WHERE(Kto = @KTO) AND (Kogo = @KOGO)", conn);
                cmd.Parameters.Add("@KTO", SqlDbType.Int);
                cmd.Parameters["@KTO"].Value = kto;
                cmd.Parameters.Add("@KOGO", SqlDbType.Int);
                cmd.Parameters["@KOGO"].Value = kogo;
                cmd.Connection.Open();
                result = Convert.ToInt32(cmd.ExecuteScalar()) > 0;
                cmd.Connection.Close();
            }
            return result;
        }

        public static List<SubscriptionData> GetSubscriptions(int userID)
        {
            List<SubscriptionData> subscriptions = new List<SubscriptionData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [Subscribes].[ID], [Subscribes].[Kto], [Subscribes].[Kogo], [Subscribes].[date], [Users].[Nick] 
                                                FROM [Subscribes] 
                                                LEFT JOIN [Users] ON [Subscribes].[Kogo] = [Users].[ID]
                                                WHERE [Subscribes].[Kto] = @UID
                                                ORDER BY [Subscribes].[date] DESC", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int);
                cmd.Parameters["@UID"].Value = userID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    SubscriptionData subscriptionData = new SubscriptionData();
                    subscriptionData.ID = Convert.ToInt32(reader["id"]);
                    subscriptionData.Kto = Convert.ToInt32(reader["Kto"]);
                    subscriptionData.Kogo = Convert.ToInt32(reader["Kogo"]);
                    subscriptionData.KogoNick = reader["Nick"].ToString();
                    subscriptionData.date = Convert.ToDateTime(reader["date"]);
                    subscriptions.Add(subscriptionData);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return subscriptions;
        }
    }
}